package com.test.mapper.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.RowMapper;

import com.test.dao.AbstractDao;
import com.test.dao.DBOperationResult;
import com.test.mapper.dao.EntityPropertyDao;
import com.test.mapper.enitites.EntityModel;
import com.test.mapper.enitites.EntityProperty;
import com.test.mapper.enitites.PropertyType;
import com.test.mapper.enitites.pk.EntityPropertyPK;

public class EntityPropertyDaoImpl extends AbstractDao implements EntityPropertyDao {

	private static final String TABLE_NAME = "columns";

	private static final String SELECT =
		"select client, table_name, column_name, property_name, property_type, field_length,"
		+ " foreign_table_name, foreign_column_name, unique_field"
		+ " from " + TABLE_NAME;

	private static final String ADD =
		"insert into " + TABLE_NAME
		+ " (client, table_name, column_name, property_name, property_type, field_length,"
		+ " foreign_table_name, foreign_column_name, unique_field)"
		+ " values (?, ?, ?, ?, ?, ?, ?, ?, ?)";

	private static final String UPDATE =
		" update " + TABLE_NAME
		+ " set property_name = ?, property_type = ?, field_length = ?,"
		+ " foreign_table_name = ?, foreign_column_name = ?, unique_field = ?"
		+ " where client = ? and table_name = ? and column_name = ?";

	private static final String REMOVE =
		" delete from " + TABLE_NAME
		+ " where client = ? and table_name = ? and column_name = ?";

	private static final Map<String, String> PROPERTY_TO_DB_COLUMN;
	static {
		final Map<String, String> temp = new HashMap<String, String>();
		temp.put("clientId", "client");
		temp.put("tableName", "table_name");
		temp.put("columnName", "column_name");
		temp.put("propertyName", "property_name");
		temp.put("propertyType", "property_type");
		temp.put("fieldLength", "filed_length");
		temp.put("unique", "unique_field");
		temp.put("foreignKeyTo", "foreign_column_name");
		temp.put("foreignModel", "foreign_table_name");
		PROPERTY_TO_DB_COLUMN = Collections.unmodifiableMap(temp);
	}

	@Override
	protected String mapPropertyNameToDBColumn(final String propertyName) {
		return PROPERTY_TO_DB_COLUMN.get(propertyName);
	}

	/*
	 * (non-Javadoc)
	 * @see com.test.dao.Dao#add(java.lang.Object)
	 */
	public DBOperationResult add(final EntityProperty newObject) {
		logger.debug("START add");

		try {
			final int rows = getSimpleJdbcTemplate().update(ADD,
					newObject.getClientId(), newObject.getTableName(), newObject.getColumnName(),
					newObject.getPropertyName(), newObject.getPropertyType().getValue(), newObject.getFieldLength(),
					newObject.getForeignModel() == null ? null : newObject.getForeignModel().getTableName(),
					newObject.getForeignKeyTo() == null ? null : newObject.getForeignKeyTo().getColumnName(),
					newObject.isUnique());
			logger.debug("Number of rows affected: " + rows);

			return getResultForAffectedRows(rows);
		} catch (DataAccessException dae) {
			logger.error("SQL error adding entity property", dae);
			return getResultForDataAccessException(dae);
		} catch (Exception e) {
			logger.error("Generic error adding entity property", e);
			return DBOperationResult.ERROR_UNKNOWN;
		} finally {
			logger.debug("END add");
		}
	}

	/*
	 * (non-Javadoc)
	 * @see com.test.dao.Dao#update(java.lang.Object)
	 */
	public DBOperationResult update(final EntityProperty object) {

		logger.debug("START update");
		logger.debug("Client ID: " + object.getClientId());
		logger.debug("Table name: " + object.getTableName());
		logger.debug("Column name: " + object.getColumnName());

		try {
			final int rows = getSimpleJdbcTemplate().update(UPDATE,
					object.getPropertyName(), object.getPropertyType().getValue(),
					object.getFieldLength(), object.getForeignModel() == null ? null : object.getForeignModel().getTableName(),
					object.getForeignKeyTo() == null ? null : object.getForeignKeyTo().getColumnName(), object.isUnique(),
					object.getClientId(), object.getTableName(), object.getColumnName());
			logger.debug("Affected rows: " + rows);

			return getResultForAffectedRows(rows);
		} catch (DataAccessException dae) {
			logger.error("SQL error updating entity property", dae);
			return getResultForDataAccessException(dae);
		} catch (Exception e) {
			logger.error("Generic error updating entity property", e);
			return DBOperationResult.ERROR_UNKNOWN;
		} finally {
			logger.debug("END update");
		}
	}

	/*
	 * (non-Javadoc)
	 * @see com.test.dao.Dao#remove(java.lang.Object)
	 */
	public DBOperationResult remove(final EntityProperty object) {

		logger.debug("START remove");
		logger.debug("Client ID: " + object.getClientId());
		logger.debug("Table name: " + object.getTableName());
		logger.debug("Column name: " + object.getColumnName());

		try {
			final int rows = getSimpleJdbcTemplate().update(REMOVE,
					object.getClientId(), object.getTableName(), object.getColumnName());
			logger.debug("Affected rows: " + rows);

			return getResultForAffectedRows(rows);
		} catch (DataAccessException dae) {
			logger.error("SQL error removing entity property", dae);
			return getResultForDataAccessException(dae);
		} catch (Exception e) {
			logger.error("Generic error removing entity property", e);
			return DBOperationResult.ERROR_UNKNOWN;
		} finally {
			logger.debug("END remove");
		}
	}

	/*
	 * (non-Javadoc)
	 * @see com.test.dao.Dao#search(java.util.Map)
	 */
	public Collection<EntityProperty> search(final Map<String, Collection<? extends Object>> propertyCriteriaMap) {

		logger.debug("START search");

		final String whereCondition = buildWhereCondition(propertyCriteriaMap);
		final String sql = StringUtils.isEmpty(whereCondition) ? SELECT : SELECT + " where " + whereCondition;

		logger.debug("SQL: " + sql);

		List<EntityProperty> result = null;

		try {
			result = getSimpleJdbcTemplate().query(sql, new EntityPropertyMapper());
		} catch (DataAccessException dae) {
			logger.error("SQL exception while searching for entity properties", dae);
		} catch (Exception e) {
			logger.error("Generic error while searching for entity properties", e);
		} finally {
			logger.debug("Result size: " + result == null ? "null" : result.size());
			logger.debug("END search");
		}

		logger.debug("END search");

		return result;
	}

	/*
	 * (non-Javadoc)
	 * @see com.test.dao.Dao#load(java.lang.Object)
	 */
	public EntityProperty load(final EntityPropertyPK pk) {
		logger.debug("START load");
		logger.debug("Primary key: " + pk);

		final String sql = SELECT + " where client = ? and table_name = ? and column_name = ?";
		try {
			final List<EntityProperty> properties = getSimpleJdbcTemplate().query(sql,
					new EntityPropertyMapper(), pk.getClientId(), pk.getTableName(), pk.getColumnName());
			if (properties.size() == 0) {
				return null;
			} else {
				if (properties.size() > 1) {
					logger.warn("Multiple entities found for PK " + pk);
				}
				return properties.get(0);
			}
		} catch (DataAccessException dae) {
			logger.error("SQL exception when loading entity property with PK " + pk, dae);
			return null;
		} catch (Exception e) {
			logger.error("Generic error loading entity property with PK " + pk, e);
			return null;
		} finally {
			logger.debug("END load");
		}
	}

	private class EntityPropertyMapper implements RowMapper<EntityProperty> {

		public EntityProperty mapRow(ResultSet rs, int rowNum) throws SQLException {
			final EntityProperty prop = new EntityProperty();
			prop.setClientId(rs.getInt("client"));
			prop.setTableName(rs.getString("table_name"));
			prop.setColumnName(rs.getString("column_name"));
			prop.setPropertyName(rs.getString("property_name"));
			prop.setPropertyType(PropertyType.getByValue(rs.getInt("property_type")));
			prop.setFieldLength(rs.getInt("field_length"));
			prop.setUnique(rs.getBoolean("unique_field"));
			final EntityModel model = new EntityModel();
			model.setTableName(rs.getString("foreign_table_name"));
			if (model.getTableName() == null) {
				prop.setForeignModel(null);
			} else {
				prop.setForeignModel(model);
			}
			final EntityProperty fk = new EntityProperty();
			fk.setColumnName(rs.getString("foreign_column_name"));
			if (fk.getColumnName() == null) {
				prop.setForeignKeyTo(null);
			} else {
				prop.setForeignKeyTo(fk);
			}
			return prop;
		}
		
	}
}
